package com.bdqn.tour.dao.impl;

import com.bdqn.tour.dao.BaseDao;
import com.bdqn.tour.dao.CityDao;
import com.bdqn.tour.pojo.City;
import com.bdqn.tour.pojo.Cityimg;
import com.bdqn.tour.pojo.Dingdan;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CityServletDaoimpl extends BaseDao implements CityDao {
    Connection conn = null;
    PreparedStatement pemt = null;
    ResultSet rs = null;

    @Override
    public int Addimg(String img) {
        //返回结果
        int row = 0;
        //连接数据库
        conn = this.getConnection();
        try {
            //创建SQL
            String sql = "INSERT INTO `part` VALUES(?)";
            //预编译
            pemt = conn.prepareStatement(sql);
            //设置参数
            pemt.setString(1, img);
            //执行
            row = pemt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeAll(conn, pemt, rs);
        }

        return row;
    }

    @Override
    public List<City> Getshow(String text) {
        //连接数据库
        conn = this.getConnection();
        //返回list集合结果
        List<City> list = new ArrayList<>();
        String sql = "";
        //判断
        if (text != null && !text.equals("全部")) {
            sql = "SELECT * FROM `city` WHERE `brand` = ?";
        } else {
            sql = "SELECT * FROM `city`";
        }
        try {
            //预编译
            pemt = conn.prepareStatement(sql);
            if (text != null && !text.equals("全部")) {
                pemt.setString(1, text);
            }
            //执行
            rs = pemt.executeQuery();
            //遍历
            while (rs.next()) {
                City city = new City();
                city.setId(rs.getInt("id"));
                city.setAddressname(rs.getString("Addressname"));
                city.setScore(rs.getDouble("score"));
                city.setPrice(rs.getInt("price"));
                city.setImg(rs.getString("img"));
                city.setBrand(rs.getString("brand"));
                city.setEvaluation(rs.getString("evaluation"));
                list.add(city);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeAll(conn, pemt, rs);
        }
        return list;
    }

    @Override
    public List<Cityimg> getjd(String id) {
        //建立返回结果listjd集合
        List<Cityimg> listjd = new ArrayList<>();
        //获取数据库连接
        conn = this.getConnection();
        //sql语句SELECT c.`Addressname`,c.`score`,c.`price`,c.`evaluation`,c.`brand`,im.`one`,im.`two`,im.`three`,im.`four`,im.`five` FROM `city` c JOIN `img` im ON c.`deid`=im.`imgid` WHERE c.`deid` = ?";
        String sql = "SELECT c.`Addressname`,c.`score`,c.`price`,c.`evaluation`,c.`brand`,im.`one`,im.`two`,im.`three`,im.`four`,im.`five` FROM `city` c JOIN `img` im ON c.`deid`=im.`imgid` WHERE c.`deid` = ?";
        try {
            //预编译
            pemt = conn.prepareStatement(sql);
            //设置参数
            pemt.setString(1, id);
            //执行
            rs = pemt.executeQuery();
            //遍历数据
            while (rs.next()) {
                //创建对象Cityimg
                Cityimg c = new Cityimg();
                c.setAddressname(rs.getString("c.Addressname"));
                c.setScore(rs.getDouble("c.score"));
                c.setPrice(rs.getInt("c.price"));
                c.setEvaluation(rs.getString("c.evaluation"));
                c.setOne(rs.getString("im.one"));
                c.setTwo(rs.getString("im.two"));
                c.setThree(rs.getString("im.three"));
                c.setFour(rs.getString("im.four"));
                c.setFive(rs.getString("im.five"));
                c.setBrand(rs.getString("c.brand"));
                listjd.add(c);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //释放资源
            this.closeAll(conn, pemt, rs);
        }
        return listjd;
    }

    @Override
    public List<City> show(int pageno, int pagesize) {
        //创建集合
        List<City> li = new ArrayList<>();
        //获取连接
        conn = this.getConnection();
        //sql语句
        String sql = "SELECT*FROM `city` LIMIT ?,?";
        try {
            //预编译
            pemt = conn.prepareStatement(sql);
            //设置参数
            pemt.setInt(1, pageno);
            pemt.setInt(2, pagesize);
            //执行
            rs = pemt.executeQuery();
            //遍历
            while (rs.next()) {
                //创建对象 City
                City city = new City();
                city.setId(rs.getInt("id"));
                city.setAddressname(rs.getString("Addressname"));
                city.setScore(rs.getDouble("score"));
                city.setPrice(rs.getInt("price"));
                city.setImg(rs.getString("img"));
                city.setBrand(rs.getString("brand"));
                city.setEvaluation(rs.getString("evaluation"));
                li.add(city);
            }
            return li;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //释放资源
            this.closeAll(conn, pemt, rs);
        }
    }

    @Override
    public int Addorder(Dingdan dingdan) {
        //返回结果
        int row = 0;
        //连接数据库
        conn = this.getConnection();
        //sql语句
        String sql = "INSERT INTO `biaodan`(`roomnumbre`,`contactPhone`,`xmname`,`sfztel`,`prices`,`brand`)VALUES(?,?,?,?,?,?)";
        try {
            //预编译
            pemt = conn.prepareStatement(sql);
            //设置参数
            pemt.setInt(1, dingdan.getRoomnumbre());
            pemt.setString(2, dingdan.getContactPhone());
            pemt.setString(3, dingdan.getXmname());
            pemt.setString(4, dingdan.getSfztel());
            pemt.setDouble(5, dingdan.getPrices());
            pemt.setString(6, dingdan.getBrand());
            //执行
            row = pemt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //释放资源
            this.closeAll(conn, pemt, rs);
        }
        return row;
    }

    @Override
    public int count() {
        //返回结果
        int sum = 0;
        try {
            //连接数据库
            conn = this.getConnection();
            //sql语句
            String sql = "SELECT COUNT(`id`) FROM `city`";
            //预编译
            pemt = conn.prepareStatement(sql);
            //执行
            rs = pemt.executeQuery();
            //遍历
            while (rs.next()) {
                //获取数据
                sum = rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //释放资源
            this.closeAll(conn, pemt, rs);
        }
        //返回结果
        return sum;
    }

    @Override
    public int YmlDelete(int id) {
        //返回结果
        int row = 0;
        //连接数据库
        conn = this.getConnection();
        try {
            //sql语句
            String sql = "DELETE FROM `city` WHERE `id` = ?";
            //预编译
            pemt = conn.prepareStatement(sql);
            //设置参数
            pemt.setInt(1, id);
            //执行
            row = pemt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeAll(conn, pemt, rs);
        }

        return row;
    }

    @Override
    public int upadtecity(String id, String Addressname, String price, String score, String brand, String evaluation) {
        //返回结果
        int row = 0;
        //连接数据库
        conn = this.getConnection();
        //sql语句
        String sql = "UPDATE `city` SET `Addressname`= ?,`score`= ?,`price`= ?,`brand`= ?,`evaluation`= ? WHERE `id` = ? ";
        try {
            //预编译
            pemt = conn.prepareStatement(sql);
            //设置参数
            pemt.setString(1, Addressname);
            pemt.setString(2, score);
            pemt.setString(3, price);
            pemt.setString(4, brand);
            pemt.setString(5, evaluation);
            pemt.setString(6, id);
            row = pemt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //释放资源
            this.closeAll(conn, pemt, rs);
        }
        //返回结果
        return row;
    }
}